﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ProgNetComponents.Log;
using ProgNetComponents.Data;

namespace ProgNetDxComponents.ExtenderProviders.Browsers
{
    public abstract class BrowserDataGrabberBase
    {
        protected Dictionary<string, DateTime> BrowserState = new Dictionary<string, DateTime>();
        public event EventHandler<LastUpdateTimeChangedEventArgs> LastUpdateTimeChanged;

        private Logger _Logger;
        protected Logger Logger
        {
            get
            {
                if (_Logger == null)
                    return Logger.Default;
                return _Logger;
            }
            set { _Logger = value; }
        }

        /// <summary>
        /// Sets table last update time
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        protected abstract bool pSetLastUpdateTime(string table);
        /// <summary>
        /// Gets table last update time or null if table was not updated
        /// </summary>
        /// <param name="b"></param>
        /// <returns></returns>
        public abstract DateTime? GetBrowserLastUpdateTimeFromDB(string browserName);

        /// <summary>
        /// Gets browsers data from database
        /// </summary>
        /// <param name="b"></param>
        /// <returns></returns>
        public abstract bool FillBrowserDataFromDB(string browserName, DataTable tableToFill, string additionalFilter = null);
        /// <summary>
        /// Gets record count for browser
        /// </summary>
        /// <param name="b"></param>
        /// <param name="additionalFilter"></param>
        /// <returns></returns>
        public abstract int GetRecordCountFromDB(string browserName, string additionalFilter = null);

        /// <summary>
        /// Sets table last update time
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool SetLastUpdateTime(string table)
        {
            bool b = pSetLastUpdateTime(table);
            if (b)
            {
                if (LastUpdateTimeChanged != null)
                {
                    LastUpdateTimeChanged(this, new LastUpdateTimeChangedEventArgs(table));
                }
            }
            return b;
        }

        /// <summary>
        /// For each Table sets last update time using SetLastUpdateTime(string table)
        /// </summary>
        /// <param name="ds"></param>
        public void SetLastUpdateTime(DataSet ds)
        {
            foreach (DataTable dt in ds.Tables)
            {
                SetLastUpdateTime(dt.TableName.XSplit("\\s+")[0]);
            }
        }
        
        /// <summary>
        /// Calls SetLastUpdateTime(string table) for each table
        /// </summary>
        /// <param name="b"></param>
        public void SetLastUpdateTime(Browser b)
        {
            if (b.Static || b.Tables.IsNull())
                return;
            foreach (string table in b.Tables)
                SetLastUpdateTime(table.XSplit("\\s+")[0]);
        }
        /// <summary>
        /// Checks if browser is up to date
        /// </summary>
        /// <param name="b"></param>
        /// <returns></returns>
        public bool IsBrowserUpToDate(Browser b)
        {
            if (b.Static)
            {
                if (!b.GetDataOnce)
                    return false;
                return BrowserCache.BrowserHasCachedData(b.Name);
            }
            DateTime? lastUpdateTime = GetBrowserLastUpdateTimeFromDB(b.Name);
            if (lastUpdateTime.HasValue)
            {
                if (BrowserState.ContainsKey(b.Name))
                {
                    return   BrowserState[b.Name] == lastUpdateTime.Value;
                }
                else
                {
                    BrowserState.Add(b.Name, lastUpdateTime.Value);
                    return true;
                }
            }
            else
                return false;
        }
        public void RefreshLastUpdateDate(Browser b)
        {
            if (b.Static)
            {
                return;
            }
            DateTime? lastUpdateTime = GetBrowserLastUpdateTimeFromDB(b.Name);
            if (lastUpdateTime.HasValue)
            {
                if (BrowserState.ContainsKey(b.Name))
                {
                    BrowserState[b.Name] = lastUpdateTime.Value;
                }
                else
                {
                    BrowserState.Add(b.Name, lastUpdateTime.Value);
                }
            }
        }
    }
    public class LastUpdateTimeChangedEventArgs : EventArgs
    {
        private string _Table;
        public string Table
        {
            get { return _Table; }
        }
        public LastUpdateTimeChangedEventArgs(string tabele)
        {
            _Table = tabele;
        }
    }
    public abstract class BrowserDataGrabberDB : BrowserDataGrabberBase
    {
        private string _TableName;
        private string _TableColumn;
        private string _DateColumn;
        public BrowserDataGrabberDB(string TableName, string TableColumn, string DateColumn, string connectionstring = null)
        {
            _TableName = TableName;
            _TableColumn = TableColumn;
            _DateColumn = DateColumn;
            if (connectionstring.IsNotNull())
            {
                Manager.UseGlobalConnectionString = false;
                Manager.ConnectionString = connectionstring;
            }
        }
        /// <summary>
        /// Gets name of table where tables last update time is stored
        /// </summary>
        public string TableName
        {
            get { return _TableName; }
        }
        /// <summary>
        /// Gets name of table name column from table where tables last update time is stored
        /// </summary>
        public string TableColumn
        {
            get { return _TableColumn; }
        }
        /// <summary>
        /// Gets name of last update time column from table where tables last update time is stored
        /// </summary>
        public string DateColumn
        {
            get { return _DateColumn; }
        }

        private DataManager _Manager = null;
        protected DataManager Manager
        {
            get {if(_Manager ==null)
                _Manager = CreateManager();
                return _Manager;
            }
        }
        /// <summary>
        /// Creates DataManager
        /// </summary>
        /// <returns></returns>
        protected abstract DataManager CreateManager();
        /// <summary>
        /// Gets DataBase function that will return current system date
        /// </summary>
        protected abstract string DBCurrentDateFunction
        {
            get;
        }
        /// <summary>
        /// Gets select string for maximum last update date for browser
        /// </summary>
        /// <param name="b"></param>
        /// <returns></returns>
        protected virtual string GetMaxDateSelect(string browserName)
        {
            string tables = "";
            Browser b = BrowserCache.GetBrowser(browserName, false);
            if (b.Tables.IsNotNull())
                foreach (string s in b.Tables)
                    tables = tables.AppendString(",") + s.EscapeSql();
            return "SELECT MAX (" + DateColumn + ") FROM " + TableName + " WHERE " + TableColumn + " IN (" + tables + ")";
        }
        /// <summary>
        /// Gets select string to retrive browsers data
        /// </summary>
        /// <param name="b"></param>
        /// <returns></returns>
        protected virtual string GetSelectString(string browserName, string additionalFilter = null)
        {
            string s = "SELECT ";
            string tmp = "";
            Browser b = BrowserCache.GetBrowser(browserName, false);
            foreach (BrowserColumn c in b.Columns)
            {
                tmp = tmp.AppendString(",");
                if (c.Expression.IsNotNull())
                    tmp += c.Expression + " AS ";
                tmp += c.Name;
            }
            s += tmp + " FROM ";
            tmp = "";
            foreach (string t in b.Tables)
                tmp = tmp.AppendString(",") + t;
            s += tmp;
            if (b.SelectFilter.IsNotNull() || additionalFilter.IsNotNull())
            {
                s += " WHERE ";
                string filter = b.SelectFilter;
                if (filter.IsNotNull())
                    filter = "(" + filter + ")";
                if (additionalFilter.IsNotNull())
                {
                    filter = filter.AppendAnd();
                    filter = "(" + additionalFilter + ")";
                }
                s += filter;
            }
            return s;
        }

        protected virtual string GetRecCountSelectString(string browserName, string additionalFilter = null)
        {
            string s = "SELECT COUNT(1) FROM ";
            string tmp = "";
            Browser b = BrowserCache.GetBrowser(browserName, false);
            foreach (string t in b.Tables)
                tmp = tmp.AppendString(",") + t;
            s += tmp;
            if (b.SelectFilter.IsNotNull() || additionalFilter.IsNotNull())
            {
                s += " WHERE ";
                string filter = b.SelectFilter;
                if (filter.IsNotNull())
                    filter = "(" + filter + ")";
                if (additionalFilter.IsNotNull())
                {
                    filter = filter.AppendAnd();
                    filter = "(" + additionalFilter + ")";
                }
                s += filter;
            }
            return s;
        }
        
        protected override bool pSetLastUpdateTime(string table)
        {
            int i = Manager.ExecuteNonQuery("UPDATE " + TableName + " SET " + DateColumn + " = " + DBCurrentDateFunction +
                " WHERE " + TableColumn + " = " + table.EscapeSql());
            if (i < 1)
            {
                Exception e = Manager.LastException;

                i = Manager.ExecuteNonQuery("INSERT INTO " + TableName + "(" + TableColumn + "," + DateColumn + ") VALUES (" +
                    table.EscapeSql() + ", " + DBCurrentDateFunction + ")");
                if (i < 0)
                {
                    Logger.Log(e, this.GetType().FullName + ".SetLastUpdateTime(string table)");
                    Logger.Log(Manager.LastException, this.GetType().FullName + ".SetLastUpdateTime(string table)");
                }
            }
            return i > 0;
        }

        public override DateTime? GetBrowserLastUpdateTimeFromDB(string browserName)
        {
            object obj = Manager.ExecuteScalar(GetMaxDateSelect(browserName));
            if (obj.IsNotNull() && obj is DateTime)
                return obj.AsDateTime();
            else
                if (Manager.LastException != null)
                    Logger.Log(Manager.LastException, "BrowserDataGrabberDB.GetBrowserLastUpdateTimeFromDB()");
            return null;
        }

        public override bool FillBrowserDataFromDB(string browserName, DataTable tableToFill, string additionalFilter = null)
        {
            if (!Manager.FillTable(tableToFill, GetSelectString(browserName, additionalFilter)))
            {
                Logger.Log(Manager.LastException, "BrowserDataGrabberDB.FillBrowserDataFromDB()");
                return false;
            }
            return true;
        }

        public override int GetRecordCountFromDB(string browserName, string additionalFilter = null)
        {
            int i = Manager.ExecuteScalar(GetRecCountSelectString(browserName, additionalFilter)).AsInt(-1);
            if(i<0)
                Logger.Log(Manager.LastException, "BrowserDataGrabberDB.GetRecordCountFromDB()");
            return i;
        }
    }
    
    public class BrowserDataGrabberSqlCe : BrowserDataGrabberDB
    {
        public BrowserDataGrabberSqlCe(string TableName, string TableColumn, string DateColumn, string connectionstring = null) : base(TableName, TableColumn, DateColumn, connectionstring) { }

        protected override DataManager CreateManager()
        {
            return new ProgNetComponents.Data.SqlCe.SqlCeDataManager();
        }

        protected override string DBCurrentDateFunction
        {
            get { return "GETDATE()"; }
        }
    }
}
